# -*- coding: utf-8 -*-
import datetime
import json
import re
from decimal import Decimal

import pandas as pd
import scrapy
from loguru import logger

from apps.listed_company.listed_company.items import NetMoneySupply, NetCreditFundsSummary
from components.config import NET_ROBOT_MYSQL_CONFIG
from utils.db.mysqldb import MysqlDB


class CreditFundsSummarySpider(scrapy.Spider):
    listed_exchange = '中国人民银行'
    name = 'credit_funds_summary'
    headers = {
        "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7",
        "Accept-Language": "zh,zh-TW;q=0.9,en-US;q=0.8,en;q=0.7,zh-CN;q=0.6",
        "Cache-Control": "max-age=0",
        "Proxy-Connection": "keep-alive",
        "Referer": "http://www.pbc.gov.cn/diaochatongjisi/116219/index.html",
        "Upgrade-Insecure-Requests": "1",
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36"
    }
    custom_settings = {
        "DUPEFILTER_DEBUG": True,
        'DUPEFILTER_CLASS': 'scrapy.dupefilters.BaseDupeFilter'
    }

    def start_requests(self):
        url = "http://www.pbc.gov.cn/diaochatongjisi/116219/116319/index.html"
        yield scrapy.Request(url, callback=self.parse_list, headers=self.headers)

    def parse_list(self, response, **kwargs):

        year_list = response.xpath('//*[@id="r_con"]//div[@class="wengao2"]')
        year_data = {i.xpath('.//a/text()').get(): i.xpath('.//a/@href').get() for i in year_list}

        for year, href in year_data.items():
            if year >= '2024年统计数据':
                url = f'http://www.pbc.gov.cn{href}'
                yield scrapy.Request(url, callback=self.parse_list2, headers=self.headers)

    def parse_list2(self, response, **kwargs):
        href = re.findall(r'<a href="(.*?)" class="lan14cu" style="margin-left:25px;">金融机构信贷收支统计', response.text)[0]

        yield scrapy.Request(f'http://www.pbc.gov.cn{href}', callback=self.parse_list3, headers=self.headers)

    def parse_list3(self, response, **kwargs):

        trs = response.xpath('//*[@id="con"]//table[@class="a2015"]//tr')
        for tr in trs:
            if '金融机构人民币信贷收支表' in tr.xpath('string(.)').get() and '存款类' not in tr.xpath('string(.)').get():
                hrefs = tr.xpath('.//a/@href').getall()
                for href in hrefs:
                    if 'htm' in href:
                        yield scrapy.Request(f'http://www.pbc.gov.cn{href}', callback=self.parse_list4, headers=self.headers)

    def parse_list4(self, response, **kwargs):
        df = pd.read_html(response.text)[0]
        df = df.drop([*range(5)], axis=0, )
        # 将第一行作为表头（列名）
        # df.columns = df.iloc[0]
        # 将第一列作为行索引（列头）
        # df.set_index(df.columns[0], inplace=True)
        lines = df.to_dict()
        for time, data in lines.items():
            if not pd.isna(data[8]):
                if '项' in data[5]:
                    continue
                if '2015' in data[5] or '2016' in data[5] or '2017' in data[5] or '2018' in data[5]:
                    item = NetCreditFundsSummary(**{
                        'time': data[5].replace('.', ''),
                        'total_deposits': Decimal(data[8]),  # 各项存款
                        'domestic_deposits': Decimal(data[9]),  # 境内存款
                        'deposits_of_households': Decimal(data[10]),  # 住户存款
                        'households_demand_deposits': Decimal(data[11]),  # 住户活期存款
                        'households_time_other_deposits': Decimal(data[12]),  # 住户定期及其他存款
                        'deposits_of_non_financial_enterprises': Decimal(data[13]),  # 非金融企业存款
                        'non_financial_enterprises_demand_deposits': Decimal(data[14]),  # 非金融企业活期存款
                        'non_financial_enterprises_time_other_deposits': Decimal(data[15]),  # 非金融企业定期及其他存款
                        # 'xxxxx': Decimal(data[16]), # 政府存款
                        'fiscal_deposits': Decimal(data[17]),  # 财政性存款
                        'deposits_of_government_departments_organizations': Decimal(data[18]),  # 机关团体存款
                        'deposits_of_non_banking_financial_institutions': Decimal(data[19]),  # 非银行业金融机构存款
                        'overseas_deposits': Decimal(data[20]),  # 境外存款
                        'financial_bonds': Decimal(data[21]),  # 金融债券
                        'currency_in_circulation': Decimal(data[22]),  # 流通中货币
                        'liabilities_to_international_financial_institutions': Decimal(data[23]),  # 对国际金融机构负债
                        'short_term': Decimal(data[24]),  # 其他
                        'total_funds_sources': Decimal(data[25]),  # 资金来源总计
                        'total_loans': Decimal(data[27]),  # 各项贷款
                        'domestic_loans': Decimal(data[28]),  # 境内贷款
                        'loans_to_households': Decimal(data[29]),  # 住户贷款
                        'households_short_term_loans': Decimal(data[30]),  # 住户短期贷款
                        'households_short_term_consumption_loans': Decimal(data[31]),  # 住户短期消费贷款
                        'households_short_term_operating_loans': Decimal(data[32]),  # 住户短期经营贷款
                        'households_mid_long_term_loans': Decimal(data[33]),  # 住户中长期贷款
                        'households_mid_long_term_consumption_loans': Decimal(data[34]),  # 住户中长期消费贷款
                        'households_mid_long_term_operating_loans': Decimal(data[35]),  # 住户中长期经营贷款
                        'loans_to_enterprises_gov': Decimal(data[36]),  # 企（事）业单位贷款
                        'short_term_loans_enterprises_gov': Decimal(data[37]),  # 企（事）业单位短期贷款
                        'mid_long_term_loans_enterprises_gov': Decimal(data[38]),  # 企（事）业单位中长期贷款
                        'paper_financing_enterprises_gov': Decimal(data[39]),  # 企（事）业单位票据融资
                        'financial_leases_enterprises_gov': Decimal(data[40]),  # 企（事）业单位融资租赁
                        'total_advances_enterprises_gov': Decimal(data[41]),  # 企（事）业单位各项垫款
                        'loans_to_non_banking_financial_organizations': Decimal(data[42]),  # 非银行业金融机构贷款
                        'overseas_loans': Decimal(data[43]),  # 境外贷款
                        'portfolio_investments': Decimal(data[44]),  # 债券投资
                        'shares_and_other_investments': Decimal(data[45]),  # 股权及其他投资
                        'position_for_bullion_purchase': Decimal(data[46]),  # 黄金占款
                        'foreign_exchange': Decimal(data[47]),  # 中央银行外汇占款
                        'assets_with_international_financial_institutions': Decimal(data[48]),  # 在国际金融机构资产
                        'total_funds_uses': Decimal(data[49]),  # 资金运用总计
                    })
                else:
                    item = NetCreditFundsSummary(**{
                        'time': data[5].replace('.', ''),
                        'total_deposits': Decimal(data[8]),  # 各项存款
                        'domestic_deposits': Decimal(data[9]),  # 境内存款
                        'deposits_of_households': Decimal(data[10]),  # 住户存款
                        'households_demand_deposits': Decimal(data[11]),  # 住户活期存款
                        'households_time_other_deposits': Decimal(data[12]),  # 住户定期及其他存款
                        'deposits_of_non_financial_enterprises': Decimal(data[13]),  # 非金融企业存款
                        'non_financial_enterprises_demand_deposits': Decimal(data[14]),  # 非金融企业活期存款
                        'non_financial_enterprises_time_other_deposits': Decimal(data[15]),  # 非金融企业定期及其他存款
                        'deposits_of_government_departments_organizations': Decimal(data[16]),  # 机关团体存款
                        'fiscal_deposits': Decimal(data[17]),  # 财政性存款
                        'deposits_of_non_banking_financial_institutions': Decimal(data[18]),  # 非银行业金融机构存款
                        'overseas_deposits': Decimal(data[19]),  # 境外存款
                        'financial_bonds': Decimal(data[20]),  # 金融债券
                        'currency_in_circulation': Decimal(data[21]),  # 流通中货币
                        'liabilities_to_international_financial_institutions': Decimal(data[22]),  # 对国际金融机构负债
                        'short_term': Decimal(data[23]),  # 其他
                        'total_funds_sources': Decimal(data[24]),  # 资金来源总计
                        'total_loans': Decimal(data[26]),  # 各项贷款
                        'domestic_loans': Decimal(data[27]),  # 境内贷款
                        'loans_to_households': Decimal(data[28]),  # 住户贷款
                        'households_short_term_loans': Decimal(data[29]),  # 住户短期贷款
                        'households_short_term_consumption_loans': Decimal(data[30]),  # 住户短期消费贷款
                        'households_short_term_operating_loans': Decimal(data[31]),  # 住户短期经营贷款
                        'households_mid_long_term_loans': Decimal(data[32]),  # 住户中长期贷款
                        'households_mid_long_term_consumption_loans': Decimal(data[33]),  # 住户中长期消费贷款
                        'households_mid_long_term_operating_loans': Decimal(data[34]),  # 住户中长期经营贷款
                        'loans_to_enterprises_gov': Decimal(data[35]),  # 企（事）业单位贷款
                        'short_term_loans_enterprises_gov': Decimal(data[36]),  # 企（事）业单位短期贷款
                        'mid_long_term_loans_enterprises_gov': Decimal(data[37]),  # 企（事）业单位中长期贷款
                        'paper_financing_enterprises_gov': Decimal(data[38]),  # 企（事）业单位票据融资
                        'financial_leases_enterprises_gov': Decimal(data[39]),  # 企（事）业单位融资租赁
                        'total_advances_enterprises_gov': Decimal(data[40]),  # 企（事）业单位各项垫款
                        'loans_to_non_banking_financial_organizations': Decimal(data[41]),  # 非银行业金融机构贷款
                        'overseas_loans': Decimal(data[42]),  # 境外贷款
                        'portfolio_investments': Decimal(data[43]),  # 债券投资
                        'shares_and_other_investments': Decimal(data[44]),  # 股权及其他投资
                        'position_for_bullion_purchase': Decimal(data[45]),  # 黄金占款
                        'foreign_exchange': Decimal(data[46]),  # 中央银行外汇占款
                        'assets_with_international_financial_institutions': Decimal(data[47]),  # 在国际金融机构资产
                        'total_funds_uses': Decimal(data[48]),  # 资金运用总计
                    })
                # print(item)
                yield item


if __name__ == "__main__":
    from scrapy import cmdline

    cmdline.execute("scrapy crawl credit_funds_summary".split())
